﻿
CREATE DATABASE [FlexySys] ON  PRIMARY 
( NAME = N'FlexySys', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FlexySys.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'FlexySys_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FlexySys_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [FlexySys] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [FlexySys] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [FlexySys] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [FlexySys] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [FlexySys] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [FlexySys] SET ARITHABORT OFF 
GO
ALTER DATABASE [FlexySys] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [FlexySys] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [FlexySys] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [FlexySys] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [FlexySys] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [FlexySys] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [FlexySys] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [FlexySys] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [FlexySys] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [FlexySys] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [FlexySys] SET  DISABLE_BROKER 
GO
ALTER DATABASE [FlexySys] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [FlexySys] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [FlexySys] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [FlexySys] SET  READ_WRITE 
GO
ALTER DATABASE [FlexySys] SET RECOVERY FULL 
GO
ALTER DATABASE [FlexySys] SET  MULTI_USER 
GO
ALTER DATABASE [FlexySys] SET PAGE_VERIFY CHECKSUM  
GO
USE [FlexySys]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [FlexySys] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

USE [FlexySys]
GO
/****** Object:  Table [dbo].[Setting]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Setting](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Key] [nvarchar](50) NOT NULL,
	[Value] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
	[ForAdministrator] [bit] NULL,
 CONSTRAINT [PK_Setting] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ListDocumentNumberingType]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ListDocumentNumberingType](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](50) NULL,
	[SortOrder] [real] NULL,
 CONSTRAINT [PK_DocumentNumberingType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[GroupTemplate]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupTemplate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NULL,
	[TemplateID] [int] NULL,
 CONSTRAINT [PK_GroupTemplate] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Group]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](200) NULL,
	[Abbreviation] [nvarchar](50) NULL,
	[Description] [nvarchar](500) NULL,
 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Exchange]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Exchange](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FromUserID] [uniqueidentifier] NULL,
	[ToUserID] [uniqueidentifier] NULL,
	[DocumentGUID] [uniqueidentifier] NULL,
	[IsRead] [bit] NULL,
	[IsDeleted] [bit] NULL,
	[Created] [datetime] NULL,
 CONSTRAINT [PK_Exchange] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DocumentType]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DocumentType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NULL,
	[Key] [nvarchar](50) NULL,
	[ListDocumentNumberingTypeID] [int] NULL,
	[Name] [nvarchar](100) NULL,
	[Abbreviation] [nvarchar](50) NULL,
	[Description] [nvarchar](100) NULL,
	[IsSystematic] [bit] NULL,
	[IsDocumentGroup] [bit] NULL,
	[SortOrder] [real] NULL,
 CONSTRAINT [PK_DocumentType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Document]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Document](
	[DocumentGUID] [uniqueidentifier] NOT NULL,
	[DocumentTypeKey] [nvarchar](50) NULL,
	[Body] [xml] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[Created] [datetime] NULL,
	[Modified] [datetime] NULL,
	[IsDraft] [bit] NULL,
	[IsDeleted] [bit] NULL,
	[Hash] [nvarchar](50) NULL,
 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED 
(
	[DocumentGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserTemplate]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserTemplate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [uniqueidentifier] NULL,
	[TemplateID] [int] NULL,
	[Allow] [bit] NULL,
	[Deny] [bit] NULL,
 CONSTRAINT [PK_UserTemplate] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[UserTemplate] ON
INSERT [dbo].[UserTemplate] ([ID], [UserId], [TemplateID], [Allow], [Deny]) VALUES (1, N'0bdd1ce8-17f5-4198-b901-d8693d36663f', 16, 1, NULL)
SET IDENTITY_INSERT [dbo].[UserTemplate] OFF
/****** Object:  Table [dbo].[UserMenu]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserMenu](
	[ID] [int] NOT NULL,
	[ParentID] [int] NULL,
	[UserId] [uniqueidentifier] NULL,
	[DefaultTemplateTypeID] [int] NULL,
	[Name] [nvarchar](50) NULL,
	[ToolTip] [nvarchar](50) NULL,
	[Target] [nvarchar](50) NULL,
	[SortOrder] [real] NULL,
 CONSTRAINT [PK_UserMenu] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserInGroup]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInGroup](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [uniqueidentifier] NULL,
	[GroupID] [int] NULL,
 CONSTRAINT [PK_UserInGroup] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TemplateType]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TemplateType](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](200) NULL,
	[Description] [nvarchar](500) NULL,
	[Abbreviation] [nvarchar](50) NULL,
	[SortOrder] [real] NULL,
 CONSTRAINT [PK_TemplateType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Template]    Script Date: 10/24/2012 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Template](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DocumentTypeKey] [nvarchar](50) NULL,
	[TemplateTypeID] [int] NULL,
	[Name] [nvarchar](100) NULL,
	[Abbreviation] [nvarchar](50) NULL,
	[Description] [nvarchar](max) NULL,
	[Body] [xml] NULL,
	[Query] [xml] NULL,
	[Active] [bit] NULL,
	[Version] [int] NULL,
	[Created] [datetime] NOT NULL,
 CONSTRAINT [PK_Template] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[vw_UserTemplates]    Script Date: 10/24/2012 15:51:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_UserTemplates]
AS

SELECT     dbo.UserInGroup.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.GroupTemplate.TemplateID
FROM         dbo.TemplateType INNER JOIN
                      dbo.Template ON dbo.TemplateType.ID = dbo.Template.TemplateTypeID INNER JOIN
                      dbo.GroupTemplate ON dbo.Template.ID = dbo.GroupTemplate.TemplateID INNER JOIN
                      dbo.UserInGroup ON dbo.GroupTemplate.GroupID = dbo.UserInGroup.GroupID

Union

SELECT     dbo.UserTemplate.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.UserTemplate.TemplateID
FROM         dbo.UserTemplate INNER JOIN
                      dbo.Template ON dbo.UserTemplate.TemplateID = dbo.Template.ID
WHERE     (dbo.UserTemplate.Allow = 1)

EXCEPT

SELECT     dbo.UserTemplate.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.UserTemplate.TemplateID
FROM         dbo.UserTemplate INNER JOIN
                      dbo.Template ON dbo.UserTemplate.TemplateID = dbo.Template.ID
WHERE     (dbo.UserTemplate.[Deny] = 1)
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[38] 4[13] 2[18] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "GroupTemplate"
            Begin Extent = 
               Top = 2
               Left = 255
               Bottom = 150
               Right = 415
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Template"
            Begin Extent = 
               Top = 0
               Left = 474
               Bottom = 218
               Right = 653
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "TemplateType"
            Begin Extent = 
               Top = 0
               Left = 705
               Bottom = 175
               Right = 865
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "UserInGroup"
            Begin Extent = 
               Top = 6
               Left = 46
               Bottom = 110
               Right = 206
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 3960
         Width = 3990
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_UserTemplates'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_UserTemplates'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_UserTemplates'
GO
/****** Object:  StoredProcedure [dbo].[sp_User_Permission_Select]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_User_Permission_Select](
 @UserId uniqueidentifier
 , @Guid uniqueidentifier
 ,@DocumentTypeKey nvarchar(50))

	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @IsDraft bit
	
	if(@Guid is not null)
	begin
		select @IsDraft = IsDraft from Document where DocumentGUID = @Guid
	end
	
	select TemplateTypeID, Version from 
		(SELECT     dbo.UserInGroup.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.Template.TemplateTypeID, dbo.Template.Active
		FROM         dbo.TemplateType INNER JOIN
							  dbo.Template ON dbo.TemplateType.ID = dbo.Template.TemplateTypeID INNER JOIN
							  dbo.GroupTemplate ON dbo.Template.ID = dbo.GroupTemplate.TemplateID INNER JOIN
							  dbo.UserInGroup ON dbo.GroupTemplate.GroupID = dbo.UserInGroup.GroupID
		UNION
		SELECT     dbo.UserTemplate.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.Template.TemplateTypeID, dbo.Template.Active
		FROM         dbo.UserTemplate INNER JOIN
							  dbo.Template ON dbo.UserTemplate.TemplateID = dbo.Template.ID
		WHERE     (dbo.UserTemplate.Allow = 1)
		EXCEPT
		SELECT     dbo.UserTemplate.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.Template.TemplateTypeID, dbo.Template.Active
		FROM         dbo.UserTemplate INNER JOIN
							  dbo.Template ON dbo.UserTemplate.TemplateID = dbo.Template.ID
		WHERE     (dbo.UserTemplate.[Deny] = 1)
		
		union
		select @UserId as UserId, @DocumentTypeKey as DocumentTypeKey, (select Version from dbo.Template where DocumentTypeKey = @DocumentTypeKey and Active=1 and TemplateTypeID = 4) as Version, 4 as TemplateTypeID, 1 as Active where @IsDraft = 1
		union
		select @UserId as UserId, @DocumentTypeKey as DocumentTypeKey, (select Version from dbo.Template where DocumentTypeKey = @DocumentTypeKey and Active=1 and TemplateTypeID = 7) as Version, 7 as TemplateTypeID, 1 as Active where @IsDraft = 1

		
		) as tblPermission
		
	where tblPermission.DocumentTypeKey = @DocumentTypeKey
			and tblPermission.UserId = @UserId
			and tblPermission.Active = 1

END
GO
/****** Object:  StoredProcedure [dbo].[sp_User_HasPermission]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_User_HasPermission](
 @UserId uniqueidentifier
 ,@Guid uniqueidentifier
 ,@DocumentTypeKey nvarchar(50)
 ,@TemplateTypeID int
 ,@Version int)

	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @IsDraft bit
	
	if(@Guid is not null)
	begin
		select @IsDraft = IsDraft from Document where DocumentGUID = @Guid
	end
	
	if exists (select * from 
		(SELECT     dbo.UserInGroup.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.Template.TemplateTypeID
		FROM         dbo.TemplateType INNER JOIN
							  dbo.Template ON dbo.TemplateType.ID = dbo.Template.TemplateTypeID INNER JOIN
							  dbo.GroupTemplate ON dbo.Template.ID = dbo.GroupTemplate.TemplateID INNER JOIN
							  dbo.UserInGroup ON dbo.GroupTemplate.GroupID = dbo.UserInGroup.GroupID
		UNION
		SELECT     dbo.UserTemplate.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.Template.TemplateTypeID
		FROM         dbo.UserTemplate INNER JOIN
							  dbo.Template ON dbo.UserTemplate.TemplateID = dbo.Template.ID
		WHERE     (dbo.UserTemplate.Allow = 1)
		EXCEPT
		SELECT     dbo.UserTemplate.UserId, dbo.Template.DocumentTypeKey, dbo.Template.Version, dbo.Template.TemplateTypeID
		FROM         dbo.UserTemplate INNER JOIN
							  dbo.Template ON dbo.UserTemplate.TemplateID = dbo.Template.ID
		WHERE     (dbo.UserTemplate.[Deny] = 1)) as tblPermission
		where tblPermission.DocumentTypeKey = @DocumentTypeKey
				and tblPermission.TemplateTypeID = @TemplateTypeID
				and (tblPermission.Version = @Version or @Version is null)
				and tblPermission.UserId = @UserId
				
		
		union
		select @UserId as UserId, @DocumentTypeKey as DocumentTypeKey, @Version as Version, 3 as TemplateTypeID where @IsDraft = 1
		union
		select @UserId as UserId, @DocumentTypeKey as DocumentTypeKey, @Version as Version, 7 as TemplateTypeID where @IsDraft = 1
	
		)
		select 1
	else
		select 0

END
GO
/****** Object:  StoredProcedure [dbo].[sp_NonGroupTemplate_Select]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_NonGroupTemplate_Select]
@GroupID int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT     dbo.Template.ID as TemplateID
, isnull(dbo.Template.Name, '')
 + '(<b>' + dbo.Template.DocumentTypeKey + N'</b>)-ի ' 
 + isnull(TemplateType.Description, '')
 + '(<b>' + dbo.TemplateType.Name + '</b>)' + isnull(N' վեր. ' +cast(dbo.Template.Version as nvarchar(10)),'') AS Name
FROM         dbo.Template INNER JOIN
                      dbo.TemplateType ON dbo.Template.TemplateTypeID = dbo.TemplateType.ID
EXCEPT

SELECT          dbo.Template.ID as TemplateID
, isnull(dbo.Template.Name, '')
 + '(<b>' + dbo.Template.DocumentTypeKey + N'</b>)-ի ' 
 + isnull(TemplateType.Description, '')
 + '(<b>' + dbo.TemplateType.Name + '</b>)' + isnull(N' վեր. ' +cast(dbo.Template.Version as nvarchar(10)),'') AS Name
FROM         dbo.Template INNER JOIN
                      dbo.TemplateType ON dbo.Template.TemplateTypeID = dbo.TemplateType.ID INNER JOIN
                      dbo.GroupTemplate ON dbo.Template.ID = dbo.GroupTemplate.TemplateID
where      dbo.GroupTemplate.GroupID = @GroupID

                      
   
END
GO
/****** Object:  StoredProcedure [dbo].[sp_GroupTemplate_Select]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GroupTemplate_Select]
@GroupID int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


SELECT     dbo.GroupTemplate.ID
			,dbo.Template.ID as TemplateID
, isnull(dbo.Template.Name, '')
 + '(<b>' + dbo.Template.DocumentTypeKey + N'</b>)-ի ' 
 + isnull(TemplateType.Description, '')
 + '(<b>' + dbo.TemplateType.Name + '</b>)' + isnull(N' վեր. ' +cast(dbo.Template.Version as nvarchar(10)),'') AS Name
FROM         dbo.Template INNER JOIN
                      dbo.TemplateType ON dbo.Template.TemplateTypeID = dbo.TemplateType.ID INNER JOIN
                      dbo.GroupTemplate ON dbo.Template.ID = dbo.GroupTemplate.TemplateID
where      dbo.GroupTemplate.GroupID = @GroupID
                      
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DocumentTypeSelectByUser]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_DocumentTypeSelectByUser] @UserId uniqueidentifier
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT     dbo.DocumentType.ID, dbo.DocumentType.ParentID, dbo.DocumentType.[Key], dbo.DocumentType.ListDocumentNumberingTypeID, dbo.DocumentType.Name, 
                      dbo.DocumentType.Abbreviation, dbo.DocumentType.Description, dbo.DocumentType.IsSystematic, dbo.DocumentType.IsDocumentGroup, dbo.DocumentType.SortOrder, 
                      t.Name AS TemplateName, t.Version, t.Active, t.ID AS TemplateID
FROM         dbo.DocumentType LEFT OUTER JOIN
                          (SELECT     ID, DocumentTypeKey, TemplateTypeID, Name, Abbreviation, Description, Body, Query, Active, Version, Created
                            FROM          dbo.Template
                            WHERE      (Active = 1) AND (TemplateTypeID = 1)) AS t ON dbo.DocumentType.[Key] = t.DocumentTypeKey
                      
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DocumentSelect]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_DocumentSelect]
 @DocumentTypeKey nvarchar(50)
 ,@IsDraft bit
 ,@IsDeleted bit
 ,@Query nvarchar(max)
 ,@UserId uniqueidentifier
 ,@TemplateTypeID int
 ,@Version int
 ,@StartIndex int
 ,@RowCount int
 ,@Count int out
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	
	SELECT     dbo.[Document].DocumentGUID, dbo.[Document].DocumentTypeKey, dbo.[Document].Body, dbo.[Document].CreatedBy, dbo.[Document].Created, 
						  dbo.[Document].Modified, dbo.[Document].IsDraft, dbo.[Document].IsDeleted
	FROM         dbo.[Document]
	WHERE dbo.[Document].DocumentTypeKey = @DocumentTypeKey
		and (dbo.[Document].IsDraft is null or dbo.[Document].IsDraft = @IsDraft or @IsDraft is null )
		and (dbo.[Document].IsDeleted is null or dbo.[Document].IsDeleted = @IsDeleted or @IsDeleted is null)
		
	SELECT     @Count = Count(dbo.[Document].DocumentGUID) 
	FROM         dbo.[Document] 
	WHERE dbo.[Document].DocumentTypeKey = @DocumentTypeKey
		and (dbo.[Document].IsDraft is null or dbo.[Document].IsDraft = @IsDraft or @IsDraft is null)
		and (dbo.[Document].IsDeleted is null or dbo.[Document].IsDeleted = @IsDeleted or @IsDeleted is null)
                      
END
GO
/****** Object:  StoredProcedure [dbo].[sp_DocumentMaxNumber_Select]    Script Date: 10/24/2012 15:51:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_DocumentMaxNumber_Select] @DocumentTypeKey nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	
	SELECT  max([Body].value('(/Document/Data/Field[@Name=''Number'']/Value)[1]', 'int')) as Number
	FROM [Document]
	where [DocumentTypeKey] = @DocumentTypeKey
		and (IsDraft <> 1 or IsDraft is null)
		and (IsDeleted <> 1 or IsDeleted is null)
END
GO
/****** Object:  Default [DF_Template_Created]    Script Date: 10/24/2012 15:51:24 ******/
ALTER TABLE [dbo].[Template] ADD  CONSTRAINT [DF_Template_Created]  DEFAULT (getdate()) FOR [Created]
GO


